package com.andy.utils.excel.t2wsl0611;

import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.StringUtils;
import com.andy.utils.excel.t2wsl0611.temp.Channel;
import org.apache.commons.collections4.CollectionUtils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class ExcelUtilService {

    public static void main(String[] args) {
        ExcelUtilService excelUtilService = new ExcelUtilService();
        //读取excel
        List<T2WslModel> demoModels = excelUtilService.readExcel();

        //opt!!!
        excelUtilService.optDemoModel(demoModels);

        //写excel
        excelUtilService.writeExcel(demoModels);

        //print sql
        for(T2WslModel t2WslModel:demoModels){
            System.out.println(t2WslModel.getSql());
        }

    }

    /**
     * 处理excel业务逻辑
     *  1-构造渠道bean
     *  2-构造SQL
     * @param demoModelList
     */
    private void optDemoModel(List<T2WslModel> demoModelList){
        //读取url的excel
        List<T2WslModel> videoModels = readT2VideoExcel();
        Map<Long,String> videoMap = new HashMap<>();
        for(T2WslModel videoModel:videoModels){
            Long pocId = videoModel.getPocId();
            String wslVideoUrl = videoModel.getWslVideoUrl();
            System.out.println(pocId);
            videoMap.put(pocId,wslVideoUrl);
        }

        //循环处理
        for(T2WslModel demoModel:demoModelList){
            //处理渠道
            String channelStr = demoModel.getChannelStr();
            if(StringUtils.isNotBlank(channelStr)){
                List<Channel> channelList = new ArrayList<>();
                String[] split = channelStr.split("、");
                for(String channelOne:split){
                    channelList.add(channelMap.get(channelOne));
                }
                demoModel.setChannelOutputJson(JSONUtil.toJsonStr(channelList));
            }else{
                demoModel.setChannelOutputJson(JSONUtil.toJsonStr(CollectionUtils.emptyCollection()));
            }

            //几个理由如果是null替换成空字符串
            if(demoModel.getWithoutBusinessLicenseReason()==null){
                demoModel.setWithoutBusinessLicenseReason("");
            }
            if(demoModel.getWithoutFoodBusinessLicenseReason()==null){
                demoModel.setWithoutFoodBusinessLicenseReason("");
            }

            //处理url
            Long pocId = demoModel.getPocId();
            if(StringUtils.isBlank(demoModel.getWslVideoUrl())){
                String wslVideoUrl = videoMap.containsKey(pocId) ? videoMap.get(demoModel.getPocId()) : videoMap.get(0L);
                demoModel.setWslVideoUrl(wslVideoUrl);
            }

            //构造Sql
            StringBuffer sb = new StringBuffer();
            // sb.append(" update poc set self_defined_name='"+demoModel.getSelfDefinedName()+"'  where id="+demoModel.getPocId()+";\r\n ");
            sb.append(" update customer_t2_extend set ");
//            sb.append(" without_business_license_reason='"+demoModel.getWithoutBusinessLicenseReason()+"', ");
//            sb.append(" without_food_business_license_reason='"+demoModel.getWithoutFoodBusinessLicenseReason()+"', ");
            sb.append(" wsl_video_url='"+demoModel.getWslVideoUrl()+"' ");
//            sb.append(" wsl_manage_channel='"+demoModel.getChannelOutputJson()+"'  ");
            sb.append(" where poc_id="+demoModel.getPocId()+"; ");
            demoModel.setSql(sb.toString());
        }
    }

    private static final String videoFileName = "C:\\Users\\ThinkPad\\Documents\\Work Files\\脚本修复文件\\二批买家修复\\2修复二批\\存量二批ignite修复 等待视频.xlsx";
    /**
     * 读视频excel
     * @return
     */
    public List<T2WslModel> readT2VideoExcel(){
        T2WslListener demoListener = new T2WslListener();
        // 这里默认读取第一个sheet
        EasyExcel.read(videoFileName, T2WslModel.class, demoListener).sheet().doRead();

        return demoListener.list;
    }

    /**
     * channel的字典
     */
    private Map<String, Channel> channelMap = new HashMap(){{
        put("简快餐",new Channel("202201","堂饮","20220101","餐饮","2022010101","简快餐","202201010101","简快餐"));
        put("西餐",new Channel("202201","堂饮","20220101","餐饮","2022010102","外国餐厅","202201010201","西餐"));
        put("日本菜",new Channel("202201","堂饮","20220101","餐饮","2022010102","外国餐厅","202201010202","日本菜"));
        put("韩国菜",new Channel("202201","堂饮","20220101","餐饮","2022010102","外国餐厅","202201010203","韩国菜"));
        put("东南亚菜",new Channel("202201","堂饮","20220101","餐饮","2022010102","外国餐厅","202201010204","东南亚菜"));
        put("其他国际美食",new Channel("202201","堂饮","20220101","餐饮","2022010102","外国餐厅","202201010205","其他国际美食"));
        put("大排档",new Channel("202201","堂饮","20220101","餐饮","2022010103","大排档","202201010301","大排档"));
        put("辣火锅",new Channel("202201","堂饮","20220101","餐饮","2022010104","火锅","202201010401","辣火锅"));
        put("南方火锅",new Channel("202201","堂饮","20220101","餐饮","2022010104","火锅","202201010402","南方火锅"));
        put("北方火锅",new Channel("202201","堂饮","20220101","餐饮","2022010104","火锅","202201010403","北方火锅"));
        put("其他火锅",new Channel("202201","堂饮","20220101","餐饮","2022010104","火锅","202201010404","其他火锅"));
        put("烤串",new Channel("202201","堂饮","20220101","餐饮","2022010105","烧烤/小龙虾","202201010501","烤串"));
        put("中式烤肉",new Channel("202201","堂饮","20220101","餐饮","2022010105","烧烤/小龙虾","202201010502","中式烤肉"));
        put("小龙虾",new Channel("202201","堂饮","20220101","餐饮","2022010105","烧烤/小龙虾","202201010503","小龙虾"));
        put("辣菜",new Channel("202201","堂饮","20220101","餐饮","2022010106","中式正餐","202201010601","辣菜"));
        put("非辣菜",new Channel("202201","堂饮","20220101","餐饮","2022010106","中式正餐","202201010602","非辣菜"));
        put("夜总会",new Channel("202201","堂饮","20220102","夜店","2022010201","夜总会","202201020101","夜总会"));
        put("量贩式KTV",new Channel("202201","堂饮","20220102","夜店","2022010202","KTV","202201020201","量贩式KTV"));
        put("家庭式KTV",new Channel("202201","堂饮","20220102","夜店","2022010202","KTV","202201020202","家庭式KTV"));
        put("慢摇吧",new Channel("202201","堂饮","20220102","夜店","2022010203","慢摇吧","202201020301","慢摇吧"));
        put("鸡尾酒吧",new Channel("202201","堂饮","20220102","夜店","2022010204","酒吧","202201020401","鸡尾酒吧"));
        put("瓶子店",new Channel("202201","堂饮","20220102","夜店","2022010204","酒吧","202201020402","瓶子店"));
        put("精酿酒吧",new Channel("202201","堂饮","20220102","夜店","2022010204","酒吧","202201020403","精酿酒吧"));
        put("音乐酒吧",new Channel("202201","堂饮","20220102","夜店","2022010204","酒吧","202201020404","音乐酒吧"));
        put("清吧",new Channel("202201","堂饮","20220102","夜店","2022010204","酒吧","202201020405","清吧"));
        put("餐吧（如拳击猫精酿餐厅）",new Channel("202201","堂饮","20220102","夜店","2022010205","餐吧","202201020501","餐吧（如拳击猫精酿餐厅）"));
        put("星级酒店",new Channel("202201","堂饮","20220103","特殊堂饮","2022010301","星级酒店","202201030101","星级酒店"));
        put("运输线路",new Channel("202201","堂饮","20220103","特殊堂饮","2022010302","运输线路","202201030201","运输线路"));
        put("网吧",new Channel("202201","堂饮","20220103","特殊堂饮","2022010303","网吧","202201030301","网吧"));
        put("咖啡厅",new Channel("202201","堂饮","20220103","特殊堂饮","2022010304","咖啡厅","202201030401","咖啡厅"));
        put("啤酒花园",new Channel("202201","堂饮","20220103","特殊堂饮","2022010305","啤酒花园","202201030501","啤酒花园"));
        put("现购自运",new Channel("202202","非堂饮","20220201","现代渠道","2022020101","现购自运","202202010101","现购自运"));
        put("大卖场",new Channel("202202","非堂饮","20220201","现代渠道","2022020102","大卖场","202202010201","大卖场"));
        put("高端超市",new Channel("202202","非堂饮","20220201","现代渠道","2022020103","高端超市","202202010301","高端超市"));
        put("普通超市",new Channel("202202","非堂饮","20220201","现代渠道","2022020104","普通超市","202202010401","普通超市"));
        put("小超市",new Channel("202202","非堂饮","20220201","现代渠道","2022020105","小超市","202202010501","小超市"));
        put("油站便利店",new Channel("202202","非堂饮","20220201","现代渠道","2022020106","油站便利店","202202010601","油站便利店"));
        put("高端便利店",new Channel("202202","非堂饮","20220201","现代渠道","2022020107","高端便利店","202202010701","高端便利店"));
        put("普通便利店",new Channel("202202","非堂饮","20220201","现代渠道","2022020108","普通便利店","202202010801","普通便利店"));
        put("杂货店",new Channel("202202","非堂饮","20220202","传统渠道","2022020201","杂货店","202202020101","杂货店"));
        put("酒行",new Channel("202202","非堂饮","20220203","非堂饮其他","2022020301","酒行","202202030101","酒行"));
        put("烟酒行",new Channel("202202","非堂饮","20220203","非堂饮其他","2022020302","烟酒行","202202030201","烟酒行"));
        put("非堂饮其他",new Channel("202202","非堂饮","20220203","非堂饮其他","2022020303","非堂饮其他","202202030301","非堂饮其他"));
    }};

    private static final String fileName = "C:\\Users\\ThinkPad\\Documents\\Work Files\\脚本修复文件\\二批买家修复\\2修复二批\\存量二批ignite修复.xlsx";
    private static final String fileNameOutput = "C:\\Users\\ThinkPad\\Documents\\Work Files\\脚本修复文件\\二批买家修复\\2修复二批\\存量二批ignite修复_output.xlsx";

    /**
     * 读excel
     * @return
     */
    public List<T2WslModel> readExcel(){
        T2WslListener demoListener = new T2WslListener();
        // 这里默认读取第一个sheet
        EasyExcel.read(fileName, T2WslModel.class, demoListener).sheet().doRead();

        return demoListener.list;
    }

    /**
     * 写excel
     * @return
     */
    public void writeExcel(List<T2WslModel> demoModelList){
        EasyExcel.write(fileNameOutput, T2WslModel.class).sheet("输出")
                .doWrite(demoModelList);
    }

}
